package flights.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.sql.PreparedStatement;

import flights.bi.Booking;

public class BookingHandler {
	
	public static List<Booking> getAllUserBookings(int nUserId) throws SQLException
	{
		List<Booking> Bookings = new ArrayList<Booking>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM booking WHERE user_id=?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nUserId);
			ResultSet rs = st.executeQuery();
			Bookings = extractBookingsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return Bookings;
	}
	
	public static List<Booking> getAllUserActiveBookings(int nUserId) throws SQLException
	{
		List<Booking> Bookings = new ArrayList<Booking>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT distinct b.* FROM booking b,tickets t, flight_seat fs, flight f WHERE b.user_id=? and t.booking_id = b.booking_id and fs.seat_id = t.seat_id and fs.flight_id = f.flight_id and f.departure_datetime > ?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nUserId);
			st.setDate(2,new Date(new java.util.Date().getTime()));
			ResultSet rs = st.executeQuery();
			Bookings = extractBookingsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return Bookings;
	}
	
	public static List<Booking> getAllUserHistoricBookings(int nUserId) throws SQLException
	{
		List<Booking> Bookings = new ArrayList<Booking>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT distinct b.* FROM booking b,ticket t, flight_seat fs, flight f WHERE b.user_id=? and t.booking_id = b.booking_id and fs.seat_id = t.seat_id and fs.flight_id = f.flight_id and f.departure_datetime =< ?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nUserId);
			st.setDate(2,new Date(new java.util.Date().getTime()));
			ResultSet rs = st.executeQuery();
			Bookings = extractBookingsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return Bookings;
	}
	
	
	public static Map<String,Integer> getBookingByMonthsForUser(int nUserId) throws SQLException
	{
		Map<String,Integer> monCount = new HashMap<String,Integer>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT MONTHNAME(BOOKING_DATE) AS month_of_booking, COUNT(*) AS booking_amount FROM booking  WHERE user_id = ? GROUP BY MONTH(BOOKING_DATE);";
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nUserId);
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				monCount.put(rs.getString("month_of_booking"),rs.getInt("booking_amount"));
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		
		conn.close();
		return monCount;
	}
	
	public static int getUserBookingCostByID(int nUserId) throws SQLException
	{
		Connection conn = DBConn.createConnection();
		String sql = "SELECT sum(cost) as total_sum FROM booking b,ticket t, flight_seat fs, flight f WHERE b.user_id=? and t.booking_id = b.booking_id and fs.seat_id = t.seat_id and fs.flight_id = f.flight_id group by b.user_id";
		int nCost = 0;
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nUserId);
			ResultSet rs = st.executeQuery();
			nCost = getSumOfColumn(rs, "total_sum");
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return nCost;
	}
	
	private static int getSumOfColumn(ResultSet resultSet, String string) throws SQLException 
	{
		int nSum = 0;
		while (resultSet.next()) {
			nSum += resultSet.getInt(string);
		}
		return nSum;
	}

	public static Booking getBookingByID(int nBookingID) throws SQLException
	{
		List<Booking> Bookings = new ArrayList<Booking>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM booking where Booking_id=?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nBookingID);
			ResultSet rs = st.executeQuery();
			Bookings = extractBookingsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		if (Bookings.size() == 0) return null;
		return Bookings.get(0);
	}
	
	public static int deleteBookingByID(int nBookingID) throws SQLException
	{
		int nRows = 0;
		nRows += TicketHandler.deleteByBooking(nBookingID);
		
		Connection conn = DBConn.createConnection();
		String sql = "DELETE FROM booking where Booking_id=?";
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nBookingID);
			nRows += st.executeUpdate();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return nRows;
	}
	
	public static int deleteEmptyBookings() throws SQLException
	{
		Connection conn = DBConn.createConnection();
		String sql = "DELETE FROM booking WHERE booking_id IN ( SELECT * FROM (SELECT a.booking_id  FROM BOOKING a LEFT JOIN ticket b ON (a.booking_id = b.booking_id) WHERE b.ticket_id IS NULL) X )";
		int nRows = 0;
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			nRows = st.executeUpdate();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return nRows;
	}
	
	public static int upsertBooking(Booking booking) throws SQLException
	{
		List<Booking> singleBooking = new ArrayList<Booking>();
		singleBooking.add(booking);
		int nRetVal = upsertBooking(singleBooking);
		return nRetVal;
	}
	
	public static int upsertBooking(List<Booking> bookings) throws SQLException
	{
		Connection conn = DBConn.createConnection();
		int nRowsAffected = 0;
		for (int i = 0; i < bookings.size(); i++) {
			if (getBookingByID(bookings.get(i).get_bookingId()) != null)
			{
				String sql = "update booking set user_id = ? , booking_date = ? , payment_method_id = ? where booking_id = ?";
				try (PreparedStatement st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)) {
					st.clearParameters();
					st.setInt(1,bookings.get(i).get_userId());
					st.setDate(2,bookings.get(i).get_bookingDate());
					st.setInt(3, bookings.get(i).get_paymentMethod());
					st.setInt(4, bookings.get(i).get_bookingId());
					nRowsAffected += st.executeUpdate();
				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
				}
			}
			else // new Booking
			{
				String sql = "insert into booking(user_id,booking_date,payment_method_id) values (?,?,?)";
				try (PreparedStatement st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)) {
					st.clearParameters();
					st.setInt(1,bookings.get(i).get_userId());
					st.setDate(2,bookings.get(i).get_bookingDate());
					st.setInt(3, bookings.get(i).get_paymentMethod());
					nRowsAffected += st.executeUpdate();
					ResultSet generatedKeys = st.getGeneratedKeys();
					if (generatedKeys.next()) {
			            nRowsAffected = generatedKeys.getInt(1);
			        } else {
			        	conn.rollback();
			            throw new SQLException("Creating user failed, no generated key obtained.");
			        }
				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
				}
			}
		}
		
		conn.close();
		return nRowsAffected;
	}
	
	private static List<Booking> extractBookingsFromRS(ResultSet resultSet) throws SQLException {
		List<Booking> Bookings = new ArrayList<Booking>();
		
		while (resultSet.next()) {
			int booking_id=resultSet.getInt("booking_id");
			int user_id=resultSet.getInt("user_id");
			Date booking_date=resultSet.getDate("booking_date");
			int payment_method_id=resultSet.getInt("payment_method_id");
			
			Booking booking = new Booking(booking_id,user_id,payment_method_id,booking_date);
			Bookings.add(booking);
		}
		
		return Bookings;
	}

}
